package org.jinion.test;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import junit.framework.TestCase;

import org.jinion.database.AbstractDatabase;
import org.jinion.database.DatabaseFactory;
import org.jinion.database.SqlConstants;
import org.jinion.database.model.AbstractResult;
import org.jinion.database.model.impl.TableRow;

public class JinionTest2 extends TestCase {
	
	AbstractDatabase database;
	
	public JinionTest2(String name) {
		super(name);
	}

	protected void setUp() throws Exception {
		super.setUp();
		database = DatabaseFactory.getDatabase("com.mysql.jdbc.Driver", "jdbc:mysql://localhost/jinion_test2", "root", "admin");
		database.delete("user");
		database.delete("website");
		
		Map map = new HashMap();
		map.put("id", "1");
		map.put("domain", "www.zhlmmc.com");
		map.put("page", "test.html");
		map.put("status", "1");
		database.insert("website", map);
		
		map.put("id", "2");
		map.put("domain", "www.aaa.com");
		map.put("page", "a.html");
		map.put("status", "1");
		database.insert("website", map);
		
		map.put("id", "3");
		map.put("domain", "www.bbb.com");
		map.put("page", "b.html");
		map.put("status", "1");
		database.insert("website", map);
		
		map.put("id", "4");
		map.put("domain", "www.ccc.com");
		map.put("page", "c.html");
		map.put("status", "2");
		database.insert("website", map);
		
		map.put("id", "1");
		map.put("name", "zhlmmc");
		map.put("website", "1");
		database.insert("user", map);
		
		map.put("id", "2");
		map.put("name", "sellseashell");
		map.put("website", "2");
		database.insert("user", map);
	}

	protected void tearDown() throws Exception {
		super.tearDown();
	}

	public void testQueryString() throws IOException, ClassNotFoundException, SQLException {
		//IDataSet ds = database.querySql("select * from website");
		
		AbstractResult result = database.executeSql("select * from website");
		
		assertEquals(result.getTable().getRows().size(), 4);
	}

	public void testQueryStringStringArray() throws IOException, ClassNotFoundException, SQLException {
		//IDataSet ds = database.querySql("select * from website where status=?", new String[]{"1"});
		
		AbstractResult result = database.executeSql("select * from website where status=?", new String[]{"1"});
		
		assertEquals(result.getTable().getRows().size(), 3);
	}
	
	public void testExecuteParameters() throws IOException, ClassNotFoundException, SQLException {
		
		database.executeSql("delete from website where page=? and status=?", new String[]{"c.html", "2"});
		//IDataSet ds = database.query("website");
		
		AbstractResult result = database.query("website");
		
		assertEquals(result.getTable().getRows().size(), 3);
	}

//	public void testQueryTableName() throws IOException, ClassNotFoundException, SQLException {
//		//IDataSet ds = database.query("website");
//		AbstractResult result = database.query("website");
//		
//		assertEquals(result.getTable().getRows().size(), 4);
//		
//		result = database.query("website", "id");
//		ResultSet rs = result.getTable().getResultSet();
//		rs.next();
//		assertEquals(1, rs.getInt("id"));
//		
//		result = database.query("website", "id", SqlConstants.DESC);
//		
//		assertEquals(new Integer(4), Integer.valueOf(result.getTable().getRows().get(1).getValue("id").toString()));
//	}
	
//	public void testQueryTableNameFields() throws IOException, ClassNotFoundException, SQLException{
//		//IDataSet ds = database.query(new String[]{}, "website");
//		AbstractResult result = database.query(new String[]{}, "website");
//		
//		assertEquals(4, result.getTable().getRows().size());
//		
//		result = database.query(new String[]{"domain", "page"}, "website");
//		assertEquals(4, result.getTable().getRows().size());
//		assertEquals("www.zhlmmc.com", result.getTable().getRows().get(1).getValue(1).toString());
//	}
	
	
	public void testBuildSqlSelection() throws IOException, ClassNotFoundException, SQLException {
		database.where("status", "1").where("id - 1 + 1", "1", SqlConstants.EQUEAL);
//		database.where("status", "1", AbstractDatabase.COMPARE_TYPE_EQUEAL);
//		database.where("id - 1 + 1", "1", AbstractDatabase.COMPARE_TYPE_EQUEAL);
		//IDataSet ds = database.query("website");
		AbstractResult result = database.query("website");
				
		assertEquals(1, result.getTable().getRows().size());
		
		database.where("status", "1", SqlConstants.EQUEAL);
		database.where("id", "1", SqlConstants.GREATER_THAN);
		result = database.query("website");

		assertEquals(2, result.getTable().getRows().size());
		
		database.where("status", "1");
		database.orWhere("id", "1");
		database.orWhere("id", "4");
		
		database.where("status", "1").orWhere("id", "1").orWhere("id", "4");
		
		result = database.query("website");

		assertEquals(4, result.getTable().getRows().size());
	}
	
//	public void testJointQuery() throws IOException, ClassNotFoundException, SQLException {
//		database.where("website", "website.id");
//		//IDataSet ds = database.query(new String[]{"user", "website"});
//		AbstractResult result = database.query(new String[]{"user", "website"});
//		
//		assertEquals(2, result.getTable().getRows().size());
//		
//		database.where("user.website", "website.id");
//		result = database.query(new String[]{"user.id", "name", "domain"}, new String[]{"user", "website"}, "user.id");
//		assertEquals(2, result.getTable().getRows().size());
//
//		assertEquals(1, result.getTable().getRows().get(1).getInt("id"));
//		
//		database.where("user.website", "website.id");
//		result = database.query(new String[]{"user.id", "name", "domain"}, new String[]{"user", "website"}, "user.id", SqlConstants.DESC);
//		
//		assertEquals(2, result.getTable().getRows().get(1).getInt("id"));
//	}
	
	public void testUpdate() throws IOException, ClassNotFoundException, SQLException {
		database.where("id", "1", SqlConstants.EQUEAL);
		Map map = new HashMap();
		map.put("domain", "www.zhlmmc.com/blog");
		map.put("status", "3");
		map.put("test", "should not appear");
		database.update("website", map);
		
		database.where("id", "1", SqlConstants.EQUEAL);
		//IDataSet ds = database.query("website");
		AbstractResult result = database.query("website");
		
		assertEquals("www.zhlmmc.com/blog", result.getTable().getRows().get(1).getValue("domain"));
		assertEquals(3, result.getTable().getRows().get(1).getInt("status"));
		
		database.where("id", "1", SqlConstants.EQUEAL);
		map = new HashMap();
		map.put("domain", "www.zhlmmc.com");
		map.put("status", "1");
		database.update("website", map);
	}
	
	public void testUpdateField() throws IOException, ClassNotFoundException, SQLException{
		database.where("id", "1", SqlConstants.EQUEAL);
		Map map = new HashMap();
		map.put("domain", "www.zhlmmc.com/blog");
		map.put("status", "3");
		map.put("test", "should not appear");
		database.update("website", map);
		
		database.where("id", "1", SqlConstants.EQUEAL);
		//IDataSet ds = database.query("website");
		AbstractResult result = database.query("website");
		assertEquals("www.zhlmmc.com/blog", result.getTable().getRows().get(1).getString("domain"));
		assertEquals(3, result.getTable().getRows().get(1).getInt("status"));
		
		database.where("id", "1");
		map = new HashMap();
		map.put("domain", "www.zhlmmc.com");
		database.update("website", map);
	}
	
	public void testUpdateFieldNumber() throws IOException, ClassNotFoundException, SQLException{
		database.where("id", "1", SqlConstants.EQUEAL);
		Map map = new HashMap();
		map.put("#2", "www.zhlmmc.com/blog");
		map.put("#4", "3");
		database.update("website", map);
		
		database.where("id", "1", SqlConstants.EQUEAL);
		//IDataSet ds = database.query("website");
		AbstractResult result = database.query("website");
		assertEquals("www.zhlmmc.com/blog", result.getTable().getRows().get(1).getString("domain"));
		assertEquals(3, result.getTable().getRows().get(1).getInt("status"));
		
		database.where("id", "1");
		map = new HashMap();
		map.put("domain", "www.zhlmmc.com");
		database.update("website", map);
	}
	
	public void testInsertAndDelete() throws IOException, ClassNotFoundException, SQLException {
		Map map = new HashMap();
		map.put("domain", "www.zhlmmc.com/blog");
		map.put("page", "index.html");
		map.put("status", "1");
		map.put("test", "should not appear");
		//int id = database.insert("website", map);
		AbstractResult result = database.insert("website", map);
		int id = (Integer)result.getGeneratedKeys().get(0);
		
		result = database.query("website");
		
		assertEquals(5, result.getTable().getRows().size());
		
		database.where("id", String.valueOf(id));
		database.delete("website");
	}
	
	public void testInsertField() throws IOException, ClassNotFoundException, SQLException{
		Map map = new HashMap();
		map.put("domain", "www.zhlmmc.com/wiki");
		map.put("page", "index.html");
		map.put("status", "1");
		map.put("test", "should not appear");
		//int id = database.insert(new String[]{"domain", "status"}, "website", map);
		AbstractResult result = database.insert("website", map);
		int id = (Integer)result.getGeneratedKeys().get(0);
		
		result = database.query("website");
		
		for (Iterator iterator = result.getTable().getRows().iterator(); iterator.hasNext();) {
			TableRow row = (TableRow) iterator.next();
			if (row.getString("domain").equals("www.zhlmmc.com/wiki") && row.getInt("status") == 1) {
				assertEquals("index.html", row.getString("page"));
			}
		}
		
		database.where("domain", "%wiki", SqlConstants.LIKE);
		result = database.query("website");
		
		if (result.getTable().getRows().size() == 1) {
			assertEquals("www.zhlmmc.com/wiki", result.getTable().getRows().get(1).getString("domain"));
		}
		else{
			fail("Compare operator \"LIKE\" doesn't work!");
		}
		
		database.where("id", String.valueOf(id), SqlConstants.EQUEAL);
		database.delete("website");
	}
	
	public void testInsertFieldNumber() throws IOException, ClassNotFoundException, SQLException{
		Map map = new HashMap();
		map.put("#2", "www.zhlmmc.com/wiki");
		map.put("#3", "index.html");
		map.put("#4", "1");
		AbstractResult result = database.insert("website", map);
		int id = (Integer)result.getGeneratedKeys().get(0);
		//int id = database.insert(new String[]{"domain", "status"}, "website", map);
		
		result = database.query("website");
		
		for (Iterator iterator = result.getTable().getRows().iterator(); iterator.hasNext();) {
			TableRow row = (TableRow) iterator.next();
			if (row.getString("domain").equals("www.zhlmmc.com/wiki") && row.getInt("status") == -1) {
				assertEquals(null, row.getString("page"));
			}
		}
		
	}
}
